﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Text;
using DBUtility;
using System.Data.OracleClient;
using System.Data;
namespace TierApplication
{
    partial class UserDao
    {
        /// <summary>
        /// 创建新用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool CreateUser(UserInfo info)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("    Insert Into P_User ");
            sql.Append("        ( usercode, username, Password, email, address, deptid, postcode, telephone, mobile) ");
            sql.Append("    Values( ");
            sql.Append("         '" + info.UserCode + "',");
            sql.Append("         '" + info.UserName + "',");
            sql.Append("         '123',");
            sql.Append("         '" + info.Email + "',");
            sql.Append("         '" + info.Address + "',");
            sql.Append("         '" + info.DeptId + "',");
            sql.Append("         '" + info.PostCode + "',");
            sql.Append("         '" + info.Telephone + "',");
            sql.Append("         '" + info.Mobile + "'");
            sql.Append("    ) ");

            return DbHelperOra.ExecuteSql(sql.ToString()) > 0;
        }
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool ModifyUser(UserInfo info)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" Update P_User ");
            sql.Append("    Set ");
            sql.Append("        username = '" + info.UserName + "', ");
            sql.Append("        email = '" + info.Email + "', ");
            sql.Append("        address = '" + info.Address + "', ");
            sql.Append("        deptid = '" + info.DeptId + "',");
            sql.Append("        postcode = '" + info.PostCode + "',");
            sql.Append("        telephone = '" + info.Telephone + "',");
            sql.Append("        mobile = '" + info.Mobile + "'");
            sql.Append("   Where userid = '" + info.UserId + "'");
            return DbHelperOra.ExecuteSql(sql.ToString()) > 0;
        }
        /// <summary>
        ///修改密码
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="OldPassword"></param>
        /// <param name="NewPassword"></param>
        /// <returns></returns>
        public bool ModifyPassword(string UserId, string OldPassword, string NewPassword)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("    Update P_User ");
            sql.Append("    Set Password = '" + NewPassword + "',");
            sql.Append("     Where userid = '" + UserId + "' and Password='" + OldPassword + "'");
            return DbHelperOra.ExecuteSql(sql.ToString()) > 0;
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public bool DeleteUser(string UserId)
        {
            string Sql = string.Format("Delete P_User Where userid = '{0}'", UserId);
            return DbHelperOra.ExecuteSql(Sql) > 0;
        }
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="UserCode"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        public UserInfo Login(string UserCode, string Password)
        {
            OracleParameter[] parameters ={
                                               new OracleParameter("AC_USERCODE",OracleType.VarChar,30),
                                 new OracleParameter("AC_PASSWORD",OracleType.VarChar,60),
                                  new OracleParameter("REFCURSOR",OracleType.Cursor)
                                                };
            parameters[0].Value = UserCode;
            parameters[1].Value = Password;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[2].Direction = ParameterDirection.Output;

            UserInfo info = null;

            DataSet ds = DbHelperOra.RunProcedure("SP_GET_USERINFO", parameters, "userinfo");
            if (ds.Tables[0].Rows.Count > 0)
            {
                info = new UserInfo();
                if (ds.Tables[0].Rows[0]["UserId"] != null && ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    info.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DeptId"] != null && ds.Tables[0].Rows[0]["DeptId"].ToString() != "")
                {
                    info.DeptId = int.Parse(ds.Tables[0].Rows[0]["DeptId"].ToString());
                }
                info.UserCode = ds.Tables[0].Rows[0]["UserCode"].ToString();
                info.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                info.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                info.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                info.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                info.PostCode = ds.Tables[0].Rows[0]["PostCode"].ToString();
                info.Telephone = ds.Tables[0].Rows[0]["Telephone"].ToString();
                info.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
            }
            return info;
        }
        /// <summary>
        /// 取得所有用户
        /// </summary>
        /// <returns></returns>
        public List<UserInfo> GetUserAll()
        {
            List<UserInfo> list = new List<UserInfo>();
            UserInfo info = null;
            string sql = "Select userid, usercode, username, Password, email, address, deptid, postcode, telephone, mobile From P_User";
            using (DbDataReader reader = DbHelperOra.ExecuteReader(sql))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        info = new UserInfo();
                        info.UserId = (reader.IsDBNull(reader.GetOrdinal("userid"))) ? int.Parse("0") : int.Parse(reader["userid"].ToString());
                        info.DeptId = (reader.IsDBNull(reader.GetOrdinal("deptid"))) ? int.Parse("0") : int.Parse(reader["deptid"].ToString());
                        info.UserCode = (reader.IsDBNull(reader.GetOrdinal("usercode"))) ? "" : reader["usercode"].ToString();
                        info.UserName = (reader.IsDBNull(reader.GetOrdinal("username"))) ? "" : reader["username"].ToString();
                        info.Password = (reader.IsDBNull(reader.GetOrdinal("Password"))) ? "" : reader["Password"].ToString();
                        info.Email = (reader.IsDBNull(reader.GetOrdinal("email"))) ? "" : reader["email"].ToString();
                        info.Address = (reader.IsDBNull(reader.GetOrdinal("address"))) ? "" : reader["address"].ToString();
                        info.PostCode = (reader.IsDBNull(reader.GetOrdinal("postcode"))) ? "" : reader["postcode"].ToString();
                        info.Telephone = (reader.IsDBNull(reader.GetOrdinal("telephone"))) ? "" : reader["telephone"].ToString();
                        info.Mobile = (reader.IsDBNull(reader.GetOrdinal("mobile"))) ? "" : reader["mobile"].ToString();
                        list.Add(info);
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// 更加条件取得用户信息
        /// </summary>
        /// <param name="Condition"></param>
        /// <returns></returns>
        public List<UserInfo> GetUserByCondition(UserInfo Condition)
        {
            List<UserInfo> list = new List<UserInfo>();
            UserInfo info = null;
            StringBuilder Sql = new StringBuilder();
            Sql.Append("Select userid, usercode, username, Password, email, address, deptid, postcode, telephone, mobile From P_User where 1=1 ");
            if (Condition != null)
            {
                if (Condition.UserId.ToString() != "0")
                    Sql.Append("    and userid='" + Condition.UserId.ToString() + "'");
                if (Condition.DeptId.ToString() != "0")
                    Sql.Append("    and deptid='" + Condition.DeptId.ToString() + "'");
                if (Condition.UserCode != "")
                    Sql.Append("    and usercode like '%" + Condition.UserCode + "%'");
                if (Condition.UserName != "")
                    Sql.Append("    and username like '%" + Condition.UserName + "%'");
                if (Condition.Telephone != "")
                    Sql.Append("    and telephone like '%" + Condition.Telephone + "%'");
                if (Condition.PostCode != "")
                    Sql.Append("    and postcode like '%" + Condition.PostCode + "%'");
                if (Condition.Email != "")
                    Sql.Append("    and email like '%" + Condition.Email + "%'");
                if (Condition.Address != "")
                    Sql.Append("    and address like '%" + Condition.Address + "%'");
                if (Condition.Mobile != "")
                    Sql.Append("    and mobile like '%" + Condition.Mobile + "%'");
            }
            using (DbDataReader reader = DbHelperOra.ExecuteReader(Sql.ToString()))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        info = new UserInfo();
                        info.UserId = (reader.IsDBNull(reader.GetOrdinal("userid"))) ? int.Parse("0") : int.Parse(reader["userid"].ToString());
                        info.DeptId = (reader.IsDBNull(reader.GetOrdinal("deptid"))) ? int.Parse("0") : int.Parse(reader["deptid"].ToString());
                        info.UserCode = (reader.IsDBNull(reader.GetOrdinal("usercode"))) ? "" : reader["usercode"].ToString();
                        info.UserName = (reader.IsDBNull(reader.GetOrdinal("username"))) ? "" : reader["username"].ToString();
                        info.Password = (reader.IsDBNull(reader.GetOrdinal("Password"))) ? "" : reader["Password"].ToString();
                        info.Email = (reader.IsDBNull(reader.GetOrdinal("email"))) ? "" : reader["email"].ToString();
                        info.Address = (reader.IsDBNull(reader.GetOrdinal("address"))) ? "" : reader["address"].ToString();
                        info.PostCode = (reader.IsDBNull(reader.GetOrdinal("postcode"))) ? "" : reader["postcode"].ToString();
                        info.Telephone = (reader.IsDBNull(reader.GetOrdinal("telephone"))) ? "" : reader["telephone"].ToString();
                        info.Mobile = (reader.IsDBNull(reader.GetOrdinal("mobile"))) ? "" : reader["mobile"].ToString();
                        list.Add(info);
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// 根据ID取得单个用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo GetUserInfo(string id)
        {
            UserInfo info = null;
            string sql = "Select userid, usercode, username, Password, email, address, deptid, postcode, telephone, mobile From P_User where userid='" + id + "'";
            using (DbDataReader reader = DbHelperOra.ExecuteReader(sql))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        info = new UserInfo();
                        info.UserId = (reader.IsDBNull(reader.GetOrdinal("userid"))) ? int.Parse("0") : int.Parse(reader["userid"].ToString());
                        info.DeptId = (reader.IsDBNull(reader.GetOrdinal("deptid"))) ? int.Parse("0") : int.Parse(reader["deptid"].ToString());
                        info.UserCode = (reader.IsDBNull(reader.GetOrdinal("usercode"))) ? "" : reader["usercode"].ToString();
                        info.UserName = (reader.IsDBNull(reader.GetOrdinal("username"))) ? "" : reader["username"].ToString();
                        info.Password = (reader.IsDBNull(reader.GetOrdinal("Password"))) ? "" : reader["Password"].ToString();
                        info.Email = (reader.IsDBNull(reader.GetOrdinal("email"))) ? "" : reader["email"].ToString();
                        info.Address = (reader.IsDBNull(reader.GetOrdinal("address"))) ? "" : reader["address"].ToString();
                        info.PostCode = (reader.IsDBNull(reader.GetOrdinal("postcode"))) ? "" : reader["postcode"].ToString();
                        info.Telephone = (reader.IsDBNull(reader.GetOrdinal("telephone"))) ? "" : reader["telephone"].ToString();
                        info.Mobile = (reader.IsDBNull(reader.GetOrdinal("mobile"))) ? "" : reader["mobile"].ToString();
                    }
                }
            }
            return info;
        }
        /// <summary>
        /// 根据用户代码取得单个用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo GetUserInfoByUserCode(string UserCode)
        {
            UserInfo info = null;
            string sql = "Select userid, usercode, username, Password, email, address, deptid, postcode, telephone, mobile From P_User where usercode='" + UserCode + "'";
            using (DbDataReader reader = DbHelperOra.ExecuteReader(sql))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        info = new UserInfo();
                        info.UserId = (reader.IsDBNull(reader.GetOrdinal("userid"))) ? int.Parse("0") : int.Parse(reader["userid"].ToString());
                        info.DeptId = (reader.IsDBNull(reader.GetOrdinal("deptid"))) ? int.Parse("0") : int.Parse(reader["deptid"].ToString());
                        info.UserCode = (reader.IsDBNull(reader.GetOrdinal("usercode"))) ? "" : reader["usercode"].ToString();
                        info.UserName = (reader.IsDBNull(reader.GetOrdinal("username"))) ? "" : reader["username"].ToString();
                        info.Password = (reader.IsDBNull(reader.GetOrdinal("Password"))) ? "" : reader["Password"].ToString();
                        info.Email = (reader.IsDBNull(reader.GetOrdinal("email"))) ? "" : reader["email"].ToString();
                        info.Address = (reader.IsDBNull(reader.GetOrdinal("address"))) ? "" : reader["address"].ToString();
                        info.PostCode = (reader.IsDBNull(reader.GetOrdinal("postcode"))) ? "" : reader["postcode"].ToString();
                        info.Telephone = (reader.IsDBNull(reader.GetOrdinal("telephone"))) ? "" : reader["telephone"].ToString();
                        info.Mobile = (reader.IsDBNull(reader.GetOrdinal("mobile"))) ? "" : reader["mobile"].ToString();
                    }
                }
            }
            return info;
        }
    }
}
